        with reback_bill as (
        select
             to_date(t.examine_time) as examine_time_f
            ,t.examine_time        --审核时间
            ,t.waybill_no
            ,case when t.apply_network_code=t.sender_network_code then 1 else 2 end as register_type --1是始发登记，2目的登记
            ,t.apply_type_code  --5转寄件,4退回件
            ,t.apply_network_code  --登记网点
            ,t.apply_network_name
            ,t.apply_time          --登记时间
            ,t.sender_network_code --揽收网点
            ,t.sender_network_name
            ,t.print_time          --面单打印时间
            ,t.print_network_name
            ,t.print_network_code
            ,t.entry_type
            ,t1.ordersource_code as order_source_code
            ,t1.ordersource_name as order_source_name
        from jms_dwd.dwd_tab_reback_transfer_express_base t
        left join (
                    select
                        t.waybill_no,t.ordersource_code,t.ordersource_name
                    from jms_dwd.dwd_wide_unsign_summary_waybill_dt t
                    where     t.dt>='{{ execution_date | date_add(-37) | cst_ds }}'  --取37天的数据
                          and t.dt<='{{ execution_date  | cst_ds }}'
                          and !( first_nodal_network_code is  null and first_center_network_code is   null )
                ) t1 on t.waybill_no=t1.waybill_no
        where  to_date(examine_time)>='{{ execution_date | date_add(-30) | cst_ds }}'  --回刷30天数据,取30天的数据
           and to_date(examine_time)<='{{ execution_date  | cst_ds }}'
              and status=2 --已审核
              and t1.waybill_no is not null
        )

        ,reback_bill_tmp1 as (
        select *
          from (
                select
                     reback_bill.examine_time_f
                    ,reback_bill.examine_time        --审核时间
                    ,date_add(reback_bill.examine_time ,1) as warehouse_time
                    ,substr(reback_bill.examine_time,12,8) as warehouse_hh
                    ,reback_bill.waybill_no
                    ,reback_bill.order_source_code as order_source_code
                    ,reback_bill.order_source_name as order_source_name
                    ,reback_bill.register_type --1是始发登记，2目的登记
                    ,reback_bill.apply_type_code  --5转寄件,4退回件
                    ,reback_bill.apply_network_code  --登记网点
                    ,reback_bill.apply_network_name
                    ,reback_bill.apply_time          --登记时间
                    ,reback_bill.sender_network_code --揽收网点
                    ,reback_bill.sender_network_name
                    ,reback_bill.print_time          --面单打印时间
                    ,reback_bill.print_network_name
                    ,reback_bill.print_network_code
                    ,row_number()over(partition by reback_bill.waybill_no order by reback_bill.examine_time ) as rn
                    ,min(case when reback_bill.examine_time < reback_bill.print_time then  reback_bill.print_time else null end )
                        over(partition by reback_bill.waybill_no) as min_print_time
                from reback_bill reback_bill
                where  reback_bill.entry_type=2 --退回件

            ) t where rn =1
        )
        ,base_scan_tmp1 as (
        --发件
         select   /*+ BROADCAST(t1) */
                  t.waybill_no
                 ,t.scan_time
                 ,t.input_site as scan_site
                 ,t.scan_site_code
                 ,t.next_station
                 ,t.next_station_code
                 ,1 as scan_type
                 ,named_struct(
                     'scan_time', scan_time
                     ,'scan_type',1
                     ,'next_station',next_station
                     ,'next_station_code',next_station_code
                     ,'scan_site',input_site
                     ,'scan_site_code',scan_site_code
                     ) as time_and_type
            from jms_dwd.dwd_tab_barscan_send_base_dt t
            join reback_bill t1 on t.waybill_no =t1.waybill_no
            where t.dt>='{{ execution_date | date_add(-37) | cst_ds }}' --取37天的数据
                  and t.dt<='{{ execution_date  | cst_ds }}'
                  and t.scan_time>='{{ execution_date | date_add(-37) | cst_ds }}'
                  and t.scan_site_code is not null
                  and t.waybill_no is not null
          union all
        --到件
         select   /*+ BROADCAST(t1) */
                  t.waybill_no
                 ,t.scan_time
                 ,t.scan_site as scan_site
                 ,t.scan_site_code
                 ,null as next_station
                 ,null  as  next_station_code
                 ,2 as scan_type
                 ,named_struct(
                     'scan_time', scan_time
                     ,'scan_type',2
                     ,'next_station', scan_site
                     ,'next_station_code', scan_site_code
                     ,'scan_site',scan_site
                     ,'scan_site_code',scan_site_code
                     ) as time_and_type
            from
                jms_dwd.dwd_tab_barscan_arrival_base_dt t
            join reback_bill t1 on t.waybill_no =t1.waybill_no
            where
                  t.dt>='{{ execution_date | date_add(-37) | cst_ds }}' --取37天的数据
                  and t.dt<='{{ execution_date  | cst_ds }}'
                  and t.scan_time>='{{ execution_date | date_add(-37) | cst_ds }}'
                  and t.scan_site_code is not null
                  and t.waybill_no is not null
        )
        ,base_efficiency_time (
            --出仓扫描
                select  /*+ BROADCAST(t1) */
                      t.waybill_no
                     ,t.scan_time
                     ,3 as scan_type
                from
                    jms_dwd.dwd_tab_barscan_deliver_base_dt t
                join reback_bill t1 on t.waybill_no =t1.waybill_no

                where  t.dt>='{{ execution_date | date_add(-37) | cst_ds }}' --取37天的数据
                       and t.dt<='{{ execution_date  | cst_ds }}'


                union all
            --入库，出库扫描
             select    /*+ BROADCAST(t1) */
                      t.waybill_no
                     ,t.scan_time
                     ,4 as scan_type
                from
                    jms_dwd.dwd_tab_barscan_collect_base_dt t
                join reback_bill t1 on t.waybill_no =t1.waybill_no

                where  t.dt>='{{ execution_date | date_add(-37) | cst_ds }}' --取37天的数据
                                  and t.dt<='{{ execution_date  | cst_ds }}'


                union all
             --转邮扫描
             select    /*+ BROADCAST(t1) */
                      t.bill_code as waybill_no
                     ,t.scan_time
                     ,5 as scan_type
                from
                    jms_dwd.dwd_tab_barscan_transfer_mail_base_dt t
                join reback_bill t1 on t.bill_code =t1.waybill_no

                where  t.dt>='{{ execution_date | date_add(-37) | cst_ds }}' --取37天的数据
                                  and t.dt<='{{ execution_date  | cst_ds }}'

                union all
             --签收扫描
             select    /*+ BROADCAST(t1) */
                      t.waybill_no
                     ,t.signed_time as scan_time
                     ,6 as scan_type
                from
                    jms_dwd.dwd_tab_barscan_sign_base_dt t
                join reback_bill t1 on t.waybill_no =t1.waybill_no

                where  t.dt>='{{ execution_date | date_add(-37) | cst_ds }}' --取37天的数据
                                  and t.dt<='{{ execution_date  | cst_ds }}'


        )
        ,base_yl_tmsnew_download_effective as (
        select
        *,min(sort_flg)over(partition by depart_network_code,arrive_network_code) as min_sort_flg
            from (
                select
                 t.depart_network_code
                ,t.depart_network_name
                ,t.arrive_network_code
                ,t.arrive_network_name
                ,t.latest_warehousing_time  --最晚入仓时间
                ,t.planned_arrival_time    --规划到达时间
                ,t.span_days_arrive    --运输时长
                ,named_struct(
                 'latest_warehousing_time', latest_warehousing_time
                 ,'span_days_arrive',span_days_arrive
                 ,'planned_arrival_time',planned_arrival_time
                 ) as sort_flg
                from
                jms_dim.dim_yl_tmsnew_download_effective_base t
                where is_enable=1   and latest_warehousing_time < planned_arrival_time
            ) t
        )
        , result_tab1(
        select /*+ BROADCAST(reback_bill) */
             reback_bill.examine_time_f
            ,max(reback_bill.examine_time )    examine_time   --审核时间
            ,reback_bill.waybill_no         --运单号
            ,reback_bill.order_source_code  order_source_code --订单来源
            ,reback_bill.order_source_name order_source_name
            ,max(reback_bill.register_type )     register_type--登记类型,1是始发登记，2目的登记
            ,max(reback_bill.apply_type_code)    apply_type_code--退转类型,5转寄件,4退回件
            ,reback_bill.apply_network_code apply_network_code
            ,reback_bill.apply_network_name apply_network_name --登记网点
            ,max(reback_bill.apply_time)         apply_time --登记时间
            ,if(max(reback_bill.register_type)=2        --登记类型是目的登记
                ,max(reback_bill.apply_network_code) --取登记网点
                ,max(case when base_scan.scan_time < reback_bill.apply_time then base_scan.time_and_type  else null end).next_station_code
                ) as max_scan_site_code --应操作网点

            ,if(max(reback_bill.register_type)=2        --登记类型是目的登记
                ,max(reback_bill.apply_network_name) --取登记网点
                ,max(case when base_scan.scan_time < reback_bill.apply_time then base_scan.time_and_type  else null end).next_station
               ) as max_scan_site --应操作网点

            ,reback_bill.print_network_name print_network_name
            ,reback_bill.print_network_code print_network_code   --实际操作网点
            ,max(reback_bill.min_print_time )    min_print_time  --首次面单打印时间

            ,min(case when base_scan.scan_time > reback_bill.examine_time
                  and base_scan.scan_type=1
                  and base_scan.scan_site_code=reback_bill.apply_network_code
                  then base_scan.scan_time  else null end ) as site_send_time --网点发件扫描时间，审核时间之后的网点发件扫描

            ,min(case when base_scan.scan_time > reback_bill.examine_time then base_scan.time_and_type  else null end).next_station_code as actual_center_code

            ,min(case when base_scan.scan_time > reback_bill.examine_time then base_scan.time_and_type  else null end).next_station as actual_center --实际交件中心

            ,min(case when base_scan.scan_time > reback_bill.examine_time
                     and base_scan.scan_type=2
                  then base_scan.scan_time  else null end ) as actual_end_time --实际到达时间，审核时间之后的，最近的到件时间

            ,max(reback_bill.warehouse_time) warehouse_time --入仓时间
            ,max(reback_bill.warehouse_hh) warehouse_hh

            ,if(min(case when reback_bill.apply_type_code =5
                    and base_efficiency.scan_time > reback_bill.min_print_time
                  then base_efficiency.scan_time end ) --时效签收时间

                <min( case when reback_bill.apply_type_code =5
                        and base_scan.scan_time > reback_bill.min_print_time
                 then base_scan.scan_time end) --到件发件时间
                 ,1,0) as is_equal_site --1是，0否
            ,min(case when base_efficiency.scan_type=3 then base_efficiency.scan_time else null end ) out_warehouse_time --出仓时间
            ,min(case when base_efficiency.scan_type=6 then base_efficiency.scan_time else null end ) sign_time --出仓时间

            ,min(case when base_efficiency.scan_type <> 3 and base_efficiency.scan_time > reback_bill.examine_time
                                                                then base_efficiency.scan_time else null end ) as  efficiency_time

            ,nvl(max(reback_bill.print_network_code ),concat('spark-',rand())) as print_network_code_join
            ,nvl(min(case when base_scan.scan_time > reback_bill.examine_time then base_scan.time_and_type  else null end).next_station_code
                    ,concat('spark-',rand())) as actual_center_code_join
        from reback_bill_tmp1 reback_bill
        left join base_scan_tmp1 base_scan  on reback_bill.waybill_no=base_scan.waybill_no
        left join base_efficiency_time base_efficiency  on reback_bill.waybill_no=base_efficiency.waybill_no
        group by
           reback_bill.examine_time_f
           ,reback_bill.waybill_no
           ,reback_bill.order_source_code
           ,reback_bill.order_source_name
           ,reback_bill.apply_network_code
           ,reback_bill.apply_network_name
           ,reback_bill.print_network_name
           ,reback_bill.print_network_code
        )
        ,
        result_tab2(

         select

             t.examine_time_f
            ,max(t.examine_time )    examine_time   --审核时间
            ,t.waybill_no         --运单号
            ,t.order_source_code  order_source_code --订单来源
            ,t.order_source_name order_source_name
            ,max(t.register_type )     register_type--登记类型,1是始发登记，2目的登记
            ,max(t.apply_type_code)    apply_type_code--退转类型,5转寄件,4退回件
            ,t.apply_network_code apply_network_code
            ,t.apply_network_name apply_network_name --登记网点
            ,max(t.apply_time)         apply_time --登记时间
            ,t.max_scan_site_code as max_scan_site_code --应操作网点
            ,t.max_scan_site  as max_scan_site --应操作网点
            ,t.print_network_name print_network_name
            ,t.print_network_code print_network_code   --实际操作网点
            ,max(t.min_print_time )    min_print_time  --首次面单打印时间
            ,max(t.site_send_time) as site_send_time --网点发件扫描时间，审核时间之后的网点发件扫描
            ,max(t.actual_center_code) as actual_center_code
            ,max(t.actual_center) as actual_center --实际交件中心
            ,max(t.actual_end_time) as actual_end_time --实际到达时间，审核时间之后的，最近的到件时间
            ,max(t.warehouse_time) warehouse_time --入仓时间
            ,max(t.warehouse_hh) warehouse_hh
            ,max(t.is_equal_site) as is_equal_site --1是，0否
            ,max(out_warehouse_time ) out_warehouse_time --出仓时间
            ,max(sign_time) sign_time
            ,max(efficiency_time) as  efficiency_time
            ,max(t.print_network_code_join ) as print_network_code_join
            ,max(t.actual_center_code_join) as actual_center_code_join
            ,min(case when t.warehouse_hh<t1.latest_warehousing_time then t1.sort_flg end ) as plan_end_time
            ,min(t1.min_sort_flg) as min_sort_flg
            ,min(case when base_efficiency.scan_type <> 5  and base_efficiency.scan_time > t.examine_time then base_efficiency.scan_time else null end ) as  efficiency_time2
         from result_tab1 t
         left join base_yl_tmsnew_download_effective t1 on t.print_network_code_join=t1.depart_network_code and t.actual_center_code_join=t1.arrive_network_code
         left join base_efficiency_time base_efficiency  on t.waybill_no=base_efficiency.waybill_no
        group by t.examine_time_f
                ,t.waybill_no
                ,t.order_source_code
                ,t.order_source_name
                ,t.apply_network_code
                ,t.apply_network_name
                ,t.print_network_name
                ,t.print_network_code
                ,t.max_scan_site_code
                ,t.max_scan_site


        )


        ,result_tab3 as (
        select
             t.examine_time_f as date_time
            ,t.examine_time   --审核时间
            ,t.waybill_no         --运单号
            ,concat(date_add(t.warehouse_time,if(t.plan_end_time.span_days_arrive is not null,t.plan_end_time.span_days_arrive,t.min_sort_flg.span_days_arrive+1)))  as timely_time --是否及时，1及时，2不及时，3不考核
            ,t.order_source_code --订单来源
            ,t.order_source_name
            ,t.register_type  --登记类型,1是始发登记，2目的登记
            ,t.apply_type_code--退转类型,5转寄件,4退回件
            ,t.apply_network_code
            ,t.apply_network_name --登记网点
            ,t.apply_time --登记时间
            ,t.max_scan_site_code as  plan_scan_site_code --应操作网点
            ,t.max_scan_site      as  plan_scan_site --应操作网点
            ,t1.network_type as plan_network_type
            ,t1.agent_code    as plan_agent_code                  --'代理区编码'
            ,t1.agent_name    as plan_agent_name                 --'代理区名称,',
            ,t1.fran_code     as plan_fran_code
            ,t1.fran_name     as plan_fran_name
            ,t1.manage_code   as plan_manage_code
            ,t1.manage_name   as plan_manage_name
            ,t.print_network_name
            ,t.print_network_code   --实际操作网点
            ,t2.network_type  as print_network_type
            ,t2.agent_code    as print_agent_code                  --'代理区编码'
            ,t2.agent_name    as print_agent_name                 --'代理区名称,',
            ,t2.fran_code     as actual_fran_code
            ,t2.fran_name     as actual_fran_name
            ,t2.manage_code   as actual_manage_code
            ,t2.manage_name   as actual_manage_name
            ,t.min_print_time  --首次面单打印时间
            ,t.site_send_time --网点发件扫描时间，审核时间之后的网点发件扫描
            ,t.actual_center_code
            ,t.actual_center --实际交件中心
            ,t.actual_end_time --实际到达时间，审核时间之后的，最近的到件时间

            ,concat(t.warehouse_time,' ',t.warehouse_hh)  as warehouse_time--入仓时间
            ,if(t.plan_end_time.latest_warehousing_time is not null
                ,t.plan_end_time.latest_warehousing_time
                ,t.min_sort_flg.latest_warehousing_time)  as latest_warehousing_time            --最晚入仓时间
            ,if(t.plan_end_time.planned_arrival_time is not null
                ,t.plan_end_time.planned_arrival_time
                ,t.min_sort_flg.planned_arrival_time)  as planned_arrival_time            -- 计划倒车时间
            ,t.is_equal_site --1是，0否
            ,t.out_warehouse_time --出仓时间
            ,t.sign_time
            ,t.efficiency_time
            ,1 as type_flg
            ,t.efficiency_time2
            ,t3.is_end_code
            ,t.examine_time_f as dt
        from result_tab2 t
        left join jms_dim.dim_network_whole_massage t1 on t.max_scan_site_code=t1.code
        left join jms_dim.dim_network_whole_massage t2 on t.print_network_code=t2.code
        left join (
                     select waybill_no
                          ,max(case when end_code in ('44','41','30','5') then 1 else 0 end) is_end_code
                    from jms_dwd.dwd_tab_end_piece_base
                    group  by  waybill_no
                  ) t3 on t.waybill_no=t3.waybill_no

        )
        ,interceptorpiece_base_bill as
        (
            select
                 t1.examine_time_f
                ,t1.examine_time
                ,t.waybill_no
                ,t1.order_source_code
                ,t1.order_source_name
                ,t1.register_type
                ,t1.apply_type_code
                ,t.registration_network_name
                ,t.registration_network_code
                ,t.registration_time
                ,t.intercept_network_name
                ,t.intercept_network_code
                ,t.intercept_time
                ,t1.print_network_name
                ,t1.print_network_code
                ,case when t.intercept_time < t1.print_time then t1.print_time else null end as min_print_time
                ,date_add(t1.examine_time ,1) as warehouse_time
                ,substr(t1.examine_time,12,8) as warehouse_hh
                ,row_number() over(partition by t.waybill_no order by t1.examine_time) as rn
            from jms_dwd.dwd_yl_oms_interceptorpiece_base t
            join (select * from reback_bill t where entry_type=1

                  ) t1 on t.waybill_no=t1.waybill_no
            where t.registration_time >='{{ execution_date | date_add(-37) | cst_ds }}'
              and t.registration_time <='{{ execution_date  | cst_ds }}'
                  and (t.status<>3 or scan_type_code = 500)

            --取37天的数据
        )
        ,interceptorpiece_base_bill_t1 as (
        select

             t.examine_time_f
            ,max(t.examine_time) examine_time  --审核时间
            ,t.waybill_no
            ,t.order_source_code order_source_code --订单来源
            ,t.order_source_name order_source_name --订单来源
            ,max(t.register_type) register_type --登记类型
            ,max(t.apply_type_code) apply_type_code --退转类型
            ,t.registration_network_name registration_network_name  --登记网点
            ,t.registration_network_code registration_network_code  --登记网点
            ,max(t.registration_time) registration_time   --登记时间

            ,max(case when base_scan.scan_time < t.examine_time then base_scan.time_and_type  else null end).next_station_code
             as max_scan_site_code --应操作网点
            ,max(case when base_scan.scan_time < t.examine_time then base_scan.time_and_type  else null end).next_station
                as max_scan_site --应操作网点
            ,t.intercept_network_name intercept_network_name  --拦截网点
            ,t.intercept_network_code intercept_network_code  --拦截网点
            ,min(t.intercept_time) intercept_time  ----拦截时间
            ,t.print_network_name print_network_name --面单打印网点
            ,t.print_network_code  print_network_code --面单打印网点
            ,min(t.min_print_time) min_print_time  --首次面单打印时间

            ,min(case when base_scan.scan_time > t.examine_time
              and base_scan.scan_type=1
              and base_scan.scan_site_code=t.intercept_network_code
              then base_scan.scan_time  else null end ) as site_send_time --网点发件扫描时间，审核时间之后的网点发件扫描

            ,min(case when base_scan.scan_time > t.examine_time then base_scan.time_and_type  else null end).next_station_code as actual_center_code

            ,min(case when base_scan.scan_time > t.examine_time then base_scan.time_and_type  else null end).next_station as actual_center --实际交件中心

            ,min(case when base_scan.scan_time > t.examine_time
                     and base_scan.scan_type=2
                  then base_scan.scan_time  else null end ) as actual_end_time --实际到达时间，审核时间之后的，最近的到件时间
            ,min(case when base_efficiency.scan_type=3 then base_efficiency.scan_time else null end ) out_warehouse_time --出仓时间
            ,min(case when base_efficiency.scan_type=6 then base_efficiency.scan_time else null end ) sign_time --出仓时间
            ,max(t.warehouse_hh) warehouse_hh
            ,max(t.warehouse_time) warehouse_time
            ,nvl(max(t.print_network_code ),concat('spark-',rand())) as print_network_code_join
            ,nvl(min(case when base_scan.scan_time > t.examine_time then base_scan.time_and_type  else null end).next_station_code
                    ,concat('spark-',rand())) as actual_center_code_join

        from interceptorpiece_base_bill t
        left join base_scan_tmp1 base_scan  on t.waybill_no=base_scan.waybill_no
        left join base_efficiency_time base_efficiency  on t.waybill_no=base_efficiency.waybill_no

        where t.rn=1
        group by t.waybill_no
                ,t.examine_time_f
                ,t.order_source_code
                ,t.order_source_name
                ,t.registration_network_name
                ,t.registration_network_code
                ,t.intercept_network_name
                ,t.intercept_network_code
                ,t.print_network_name
                ,t.print_network_code

        )
        ,interceptorpiece_base_bill_t2 as (

         select

             t.examine_time_f
            ,max(t.examine_time) examine_time  --审核时间
            ,t.waybill_no
            ,t.order_source_code order_source_code --订单来源
            ,t.order_source_name order_source_name --订单来源
            ,max(t.register_type) register_type --登记类型
            ,max(t.apply_type_code) apply_type_code --退转类型
            ,t.registration_network_name registration_network_name  --登记网点
            ,t.registration_network_code registration_network_code  --登记网点
            ,max(t.registration_time) registration_time   --登记时间
            ,t.max_scan_site_code as max_scan_site_code --应操作网点
            ,t.max_scan_site  as max_scan_site --应操作网点
            ,t.intercept_network_name intercept_network_name  --拦截网点
            ,t.intercept_network_code intercept_network_code  --拦截网点
            ,min(t.intercept_time) intercept_time  ----拦截时间
            ,t.print_network_name print_network_name --面单打印网点
            ,t.print_network_code  print_network_code --面单打印网点
            ,max(t.min_print_time) min_print_time  --首次面单打印时间
            ,max(t.site_send_time) as site_send_time --网点发件扫描时间，审核时间之后的网点发件扫描
            ,max(t.actual_center_code) as actual_center_code
            ,max(t.actual_center) as actual_center --实际交件中心
            ,max(t.actual_end_time) as actual_end_time --实际到达时间，审核时间之后的，最近的到件时间
            ,max(out_warehouse_time) out_warehouse_time
            ,max(sign_time) sign_time
            ,max(t.warehouse_hh) warehouse_hh
            ,max(t.warehouse_time) warehouse_time
            ,max(t.print_network_code_join ) as print_network_code_join
            ,max(t.actual_center_code_join) as actual_center_code_join
            ,min(case when t.warehouse_hh<t1.latest_warehousing_time then t1.sort_flg end ) as  plan_end_time--规划到达时间
            ,max(t1.min_sort_flg) as min_sort_flg

            ,min(case when base_efficiency.scan_type <> 5  and base_efficiency.scan_time > t.examine_time  then base_efficiency.scan_time else null end ) as  efficiency_time2

         from interceptorpiece_base_bill_t1 t
         left join base_yl_tmsnew_download_effective t1 on t.print_network_code_join=t1.depart_network_code and t.actual_center_code_join=t1.arrive_network_code
         left join base_efficiency_time base_efficiency  on t.waybill_no=base_efficiency.waybill_no

        group by t.waybill_no
                ,t.examine_time_f
                ,t.order_source_code
                ,t.order_source_name
                ,t.registration_network_name
                ,t.registration_network_code
                ,t.intercept_network_name
                ,t.intercept_network_code
                ,t.print_network_name
                ,t.print_network_code
                ,t.max_scan_site_code
                ,t.max_scan_site

        )
        ,interceptorpiece_base_bill_t3 as (
        select
             t.examine_time_f
            ,t.examine_time   --审核时间
            ,t.waybill_no
            ,concat(date_add(t.warehouse_time,if(t.plan_end_time.span_days_arrive is not null,t.plan_end_time.span_days_arrive,t.min_sort_flg.span_days_arrive+1)))  as timely_time --是否及时，1及时，2不及时，3不考核
            ,t.order_source_code order_source_code --订单来源
            ,t.order_source_name order_source_name --订单来源
            ,t.register_type register_type --登记类型
            ,t.apply_type_code apply_type_code --退转类型
            ,t.registration_network_name registration_network_name  --登记网点
            ,t.registration_network_code registration_network_code  --登记网点
            ,t.registration_time registration_time   --登记时间
            ,t.max_scan_site_code as plan_scan_site_code --应操作网点
            ,t.max_scan_site      as plan_scan_site --应操作网点
            ,t1.network_type  as plan_network_type
            ,t1.agent_code    as plan_agent_code                  --'代理区编码'
            ,t1.agent_name    as plan_agent_name                 --'代理区名称,',
            ,t1.fran_code     as plan_fran_code
            ,t1.fran_name     as plan_fran_name
            ,t1.manage_code   as plan_manage_code
            ,t1.manage_name   as plan_manage_name
            ,t.intercept_network_name intercept_network_name  --拦截网点
            ,t.intercept_network_code intercept_network_code  --拦截网点
            ,t2.network_type   as intercept_network_type
            ,t2.agent_code    as actual_agent_code                  --'代理区编码'
            ,t2.agent_name    as actual_agent_name                 --'代理区名称,',
            ,t2.fran_code     as actual_fran_code
            ,t2.fran_name     as actual_fran_name
            ,t2.manage_code   as actual_manage_code
            ,t2.manage_name   as actual_manage_name
            ,t.intercept_time intercept_time  ----拦截时间
            ,t.print_network_name print_network_name --面单打印网点
            ,t.print_network_code  print_network_code --面单打印网点
            ,t.min_print_time min_print_time  --首次面单打印时间
            ,t.site_send_time --网点发件扫描时间，审核时间之后的网点发件扫描
            ,t.actual_center_code
            ,t.actual_center --实际交件中心
            ,t.actual_end_time --实际到达时间，审核时间之后的，最近的到件时间
            ,out_warehouse_time out_warehouse_time
            ,sign_time sign_time
            ,t.warehouse_hh warehouse_hh
            ,t.warehouse_time warehouse_time
            ,if(t.plan_end_time.latest_warehousing_time is not null
                ,t.plan_end_time.latest_warehousing_time
                ,t.min_sort_flg.latest_warehousing_time)  as latest_warehousing_time            --最晚入仓时间

            ,if(t.plan_end_time.planned_arrival_time is not null
                ,t.plan_end_time.planned_arrival_time
                ,t.min_sort_flg.planned_arrival_time)  as planned_arrival_time            -- 计划倒车时间

            ,t.efficiency_time2
            ,t3.is_end_code
        from interceptorpiece_base_bill_t2 t
        left join jms_dim.dim_network_whole_massage t1 on t.max_scan_site_code=t1.code
        left join jms_dim.dim_network_whole_massage t2 on t.intercept_network_code=t2.code
        left join (
                     select waybill_no
                          ,max(case when end_code in ('44','41','30','5') then 1 else 0 end) is_end_code
                    from jms_dwd.dwd_tab_end_piece_base
                    group  by  waybill_no
                  ) t3 on t.waybill_no=t3.waybill_no
        )
        insert overwrite table jms_dm.dm_reback_timely_rate partition ( dt )
        select
             t.date_time as date_time
            ,t.examine_time   --审核时间
            ,t.waybill_no         --运单号
            ,case when t.plan_network_type=4 then 3
                  when t.print_network_type =4 then 3
                  when t.is_end_code =1 then 3
                  when  t.actual_end_time <= concat(t.timely_time,' ', t.planned_arrival_time,':00') and t.min_print_time is not null then 1
                  when  t.min_print_time is not null and (((unix_timestamp(t.actual_end_time)-unix_timestamp(t.examine_time))/(3600*24)<=1) or ((unix_timestamp(t.site_send_time)-unix_timestamp(t.examine_time))/(3600*24)<=1)) then 1
                  when  t.apply_type_code =5 and t.min_print_time is not null and (site_send_time is null and actual_end_time is null ) and
                        ((unix_timestamp(t.examine_time)-unix_timestamp(t.efficiency_time2))/(3600*24)<=1) then 1

                  else 2 end  as is_timely --是否及时，1及时，2不及时，3不考核
            ,t.order_source_code --订单来源
            ,t.order_source_name --订单来源
            ,t.register_type  --登记类型,1是始发登记，2目的登记
            ,t.apply_type_code--退转类型,5转寄件,4退回件
            ,t.apply_network_code
            ,t.apply_network_name --登记网点
            ,t.apply_time --登记时间
            ,t.plan_scan_site_code as  plan_scan_site_code --应操作网点
            ,t.plan_scan_site      as  plan_scan_site --应操作网点
            ,t.plan_agent_code    as   plan_agent_code                  --代理区编码
            ,t.plan_agent_name    as   plan_agent_name                 --代理区名称
            ,t.plan_fran_code
            ,t.plan_fran_name
            ,t.plan_manage_code
            ,t.plan_manage_name
            ,t.print_network_name  as  actual_network_name
            ,t.print_network_code  as  actual_network_code --实际操作网点
            ,t.print_agent_code    as  actual_agent_code                  --代理区编码
            ,t.print_agent_name    as  actual_agent_name                 --代理区名称
            ,t.actual_fran_code
            ,t.actual_fran_name
            ,t.actual_manage_code
            ,t.actual_manage_name

            ,null as print_network_name --面单打印网点
            ,null as print_network_code --面单打印网点
            ,t.min_print_time  --首次面单打印时间
            ,t.site_send_time --网点发件扫描时间，审核时间之后的网点发件扫描
            ,if(t.actual_center_code=t.print_network_code,null,t.actual_center_code) as actual_center_code
            ,if(t.actual_center_code=t.print_network_code,null,t.actual_center)  as actual_center --实际交件中心
            ,t.actual_end_time --实际到达时间，审核时间之后的，最近的到件时间
            ,concat(t.timely_time,' ', t.planned_arrival_time,':00')   as plan_end_time
            ,t.warehouse_time--入仓时间
            ,t.latest_warehousing_time            --最晚入仓时间
            ,t.is_equal_site --1是，0否
            ,t.out_warehouse_time --出仓时间
            ,t.efficiency_time
            ,t.type_flg
            ,case when t.examine_time is not null and (t.min_print_time is null or (unix_timestamp(t.min_print_time) - unix_timestamp(t.examine_time)) / 3600 > 24 ) then 1
                 when t.min_print_time is not null and  t.examine_time is not null and unix_timestamp(t.min_print_time) <= (unix_timestamp(t.examine_time)+(3600*24) )
                         and t.site_send_time is null and t.out_warehouse_time is null and t.actual_end_time is null and t.sign_time is null  then 2
                 when t.min_print_time is not null and  t.examine_time is not null and unix_timestamp(t.min_print_time) <= (unix_timestamp(t.examine_time)+(3600*24))
                         and t.site_send_time is null and (t.out_warehouse_time is not null or t.actual_end_time is not null or t.sign_time is not null)  then 3
                 when t.print_network_code<>t.plan_scan_site_code then 4
                 when  t.actual_end_time>concat(t.timely_time,' ', t.planned_arrival_time,':00') and t.actual_end_time is not null then 5
                 when   t.site_send_time is not null and t.actual_end_time is null  then 6 end as  duty_cal --v2新增

            ,nvl(t.print_network_code,t.plan_scan_site_code) as site_code
            ,nvl(t.print_network_name,t.plan_scan_site)   as    site_name
            ,nvl(t.print_agent_code,t.plan_agent_code) as   site_agent_code
            ,nvl(t.print_agent_name,t.plan_agent_name) as site_agent_name
            ,nvl(t.actual_fran_code,t.plan_fran_code) as site_fran_code
            ,nvl(t.actual_fran_name,t.plan_fran_name) as site_fran_name
            ,nvl(t.actual_manage_code,t.plan_manage_code) as site_manage_code
            ,nvl(t.actual_manage_name,t.plan_manage_name) as site_manage_name
            ,null as intercept_time
            ,case when (t1.is_shaidan+t1.is_delay+t1.is_new_open+t1.is_special_report ) >=1 then 1 else 0 end as is_error
            ,t.dt
        from result_tab3 t
        left join jms_dim.dim_network_abnormal_type_union_dt t1 on t1.dt=date_add('{{ execution_date  | cst_ds }}', -2) and nvl(t.print_network_code,t.plan_scan_site_code) = t1.network_code
        where t.dt=date_add('{{ execution_date  | cst_ds }}', -2)
        union all
        select
             t.examine_time_f as date_time
            ,t.examine_time   --审核时间
            ,t.waybill_no         --运单号
            ,case when t.plan_network_type=4 then 3
                  when t.intercept_network_type=4 then 3
                  when t.is_end_code =1 then 3
                  when t.intercept_network_code=t.registration_network_code then 3
                  when t.intercept_network_code=t.registration_network_code then 3 --v2新增

                  when  t.actual_end_time <= concat(t.timely_time,' ', t.planned_arrival_time,':00') and t.min_print_time is not null then 1
                  when  t.min_print_time is not null and (((unix_timestamp(t.actual_end_time)-unix_timestamp(t.examine_time))/(3600*24)<=1) or ((unix_timestamp(t.site_send_time)-unix_timestamp(t.examine_time))/(3600*24)<=1)) then 1
                  when  t.apply_type_code =5 and t.min_print_time is not null and (site_send_time is null and actual_end_time is null ) and
                        ((unix_timestamp(t.examine_time)-unix_timestamp(t.efficiency_time2))/(3600*24)<=1) then 1

                  else 2 end  as is_timely --是否及时，1及时，2不及时，3不考核
            ,t.order_source_code --订单来源
            ,t.order_source_name
            ,t.register_type  --登记类型,1是始发登记，2目的登记
            ,t.apply_type_code--退转类型,5转寄件,4退回件
            ,t.registration_network_code
            ,t.registration_network_name --登记网点
            ,t.registration_time --登记时间
            ,t.plan_scan_site_code as  plan_scan_site_code --应操作网点
            ,t.plan_scan_site      as  plan_scan_site --应操作网点
            ,t.plan_agent_code     as   plan_agent_code
            ,t.plan_agent_name     as   plan_agent_name
            ,t.plan_fran_code
            ,t.plan_fran_name
            ,t.plan_manage_code
            ,t.plan_manage_name
            ,t.intercept_network_name as actual_network_name
            ,t.intercept_network_code  as actual_network_code  --实际操作网点
            ,t.actual_agent_code    as actual_agent_code
            ,t.actual_agent_name    as actual_agent_name
            ,t.actual_fran_code
            ,t.actual_fran_name
            ,t.actual_manage_code
            ,t.actual_manage_name
            ,t.print_network_name as print_network_name --面单打印网点
            ,t.print_network_code as print_network_code --面单打印网点
            ,t.min_print_time  --首次面单打印时间
            ,t.site_send_time --网点发件扫描时间，审核时间之后的网点发件扫描
            ,if(t.actual_center_code=t.print_network_code,null,t.actual_center_code) as actual_center_code
            ,if(t.actual_center_code=t.print_network_code,null,t.actual_center)  as actual_center --实际交件中心
            ,t.actual_end_time --实际到达时间，审核时间之后的，最近的到件时间
            ,concat(t.timely_time,' ', t.planned_arrival_time,':00')   as plan_end_time
            ,null as warehouse_time--入仓时间
            ,null as latest_warehousing_time            --最晚入仓时间
            ,null as is_equal_site --1是，0否
            ,null as out_warehouse_time --出仓时间
            ,null as efficiency_time
            ,2 as type_flg
            ,case when  t.examine_time is not null and (t.min_print_time is null or (unix_timestamp(t.min_print_time) - unix_timestamp(t.examine_time)) / 3600 > 24 ) then 1
                 when t.min_print_time is not null and  t.examine_time is not null and unix_timestamp(t.min_print_time) <= (unix_timestamp(t.examine_time)+(3600*24) )
                         and t.site_send_time is null and t.out_warehouse_time is null and t.actual_end_time is null and t.sign_time is null  then 2
                 when t.min_print_time is not null and  t.examine_time is not null and unix_timestamp(t.min_print_time) <= (unix_timestamp(t.examine_time)+(3600*24) )
                         and t.site_send_time is null and (t.out_warehouse_time is not null or t.actual_end_time is not null or t.sign_time is not null)  then 3
                 when t.intercept_network_code<>t.plan_scan_site_code then 4
                 when  t.actual_end_time>concat(t.timely_time,' ', t.planned_arrival_time,':00')  and t.actual_end_time is not null then 5
                 when  t.site_send_time is not null and t.actual_end_time is null  then 6 end as  duty_cal --v2新增

            ,nvl(t.intercept_network_code,t.plan_scan_site_code) as    site_code
            ,nvl(t.intercept_network_name,t.plan_scan_site) as    site_name
            ,nvl(t.actual_agent_code,t.plan_agent_code) as    site_agent_code
            ,nvl(t.actual_agent_name,t.plan_agent_name) as    site_agent_name
            ,nvl(t.actual_fran_code,t.plan_fran_code) as    site_fran_code
            ,nvl(t.actual_fran_name,t.plan_fran_name) as    site_fran_name
            ,nvl(t.actual_manage_code,t.plan_manage_code) as    site_manage_code
            ,nvl(t.actual_manage_name,t.plan_manage_name) as    site_manage_name
            ,t.intercept_time
            ,case when (t1.is_shaidan+t1.is_delay+t1.is_new_open+t1.is_special_report ) >=1 then 1 else 0 end as is_error

            ,t.examine_time_f as dt
        from interceptorpiece_base_bill_t3 t
        left join jms_dim.dim_network_abnormal_type_union_dt t1 on t1.dt=date_add('{{ execution_date  | cst_ds }}', -2) and nvl(t.intercept_network_code,t.plan_scan_site_code) = t1.network_code

        where t.examine_time_f = date_add('{{ execution_date  | cst_ds }}', -2)
 distribute by pmod(hash(rand()), 3)
        ;

